USE [ManagerEmployees]
GO


-- 8/3
CREATE VIEW View_Districts
AS
SELECT		Districts.*, Provinces.NameOfProvince
FROM        Districts INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID
GO


CREATE VIEW View_Communes
AS
SELECT			Communes.*, Districts.NameOfDist AS Expr1, Provinces.NameOfProvince, Districts.DistID AS Expr2, Provinces.ProvID
FROM			Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
							INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID
GO



CREATE VIEW View_Ogranizations
AS
SELECT				Organizations.*, Communes.ComID AS Expr1, Communes.NameOfComm, Districts.DistID, Districts.NameOfDist, Provinces.ProvID, Provinces.NameOfProvince
FROM				Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
								INNER JOIN Organizations ON Communes.ComID = Organizations.ComID 
								INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID
GO


CREATE VIEW View_Departments
AS
SELECT	Communes.ComID AS Expr1, Communes.NameOfComm, Districts.DistID, Districts.NameOfDist, Provinces.ProvID, 
		Provinces.NameOfProvince, Departments.*, Organizations.OrgID AS Expr2, Organizations.NameOfOrgan
FROM	Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
					INNER JOIN Organizations ON Communes.ComID = Organizations.ComID 
					INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID 
					INNER JOIN Departments ON Organizations.OrgID = Departments.OrgID
GO






--16/3/2013
CREATE PROC Proc_EducationForEmployees_EmpID
@EmpID			INT
AS
SELECT			EducationsForEmps.*, 
				Qualifications.NameOfQua, 
				PoliticalOfTheories.NameOfPoli, 
				TypeOfLanguages.NameOfLangs, 
				TypeOfInformations.NameOfInfor, 
				StateOfManagements.NameOfState, 
				Employees.FirstName,  Employees.LastName, Employees.Code, Employees.AvatarUrl, Employees.Gender, Employees.Email, Employees.Telephone
FROM            EducationsForEmps INNER JOIN
                Employees ON EducationsForEmps.EmpID = Employees.EmpID INNER JOIN
                PoliticalOfTheories ON EducationsForEmps.PoliID = PoliticalOfTheories.PoliID INNER JOIN
                Qualifications ON EducationsForEmps.QuaID = Qualifications.QuaID INNER JOIN
                StateOfManagements ON EducationsForEmps.StateID = StateOfManagements.StateID INNER JOIN
                TypeOfInformations ON EducationsForEmps.InforID = TypeOfInformations.InforID INNER JOIN
                TypeOfLanguages ON EducationsForEmps.LanguageID = TypeOfLanguages.LanguageID
WHERE			EducationsForEmps.EmpID = @EmpID
GO



-- 17/2/2013 View TrainingFosterings by IDEmp
CREATE PROC Proc_TrainningFosterings_EmpID
@EmpID			INT
AS
SELECT			TrainingFosterings.*, 
				Employees.FirstName,  Employees.LastName, Employees.Code, Employees.AvatarUrl, Employees.Gender, Employees.Email, Employees.Telephone,
				Qualifications.CodeOfQua, Qualifications.NameOfQua,
				TypeOfTrainnings.NameOfTrain
FROM            Employees INNER JOIN
                TrainingFosterings ON Employees.EmpID = TrainingFosterings.EmpID INNER JOIN
                Qualifications ON TrainingFosterings.QuaID = Qualifications.QuaID INNER JOIN
                TypeOfTrainnings ON TrainingFosterings.TrainID = TypeOfTrainnings.TrainID
WHERE			TrainingFosterings.EmpID = @EmpID
GO

CREATE PROC Proc_FamilyRelationships_EmpID
@EmpID			INT
AS
SELECT			FamilyRelationships.*, 
				Employees.FirstName,  Employees.LastName, Employees.Code, Employees.AvatarUrl, Employees.Gender, Employees.Email, Employees.Telephone
FROM            Employees INNER JOIN
				FamilyRelationships ON Employees.EmpID = FamilyRelationships.EmpID
WHERE			FamilyRelationships.EmpID = @EmpID
GO



CREATE PROC Proc_EmployeesSalaryLevels_EmpID
@EmpID			INT
AS
SELECT			EmployeesSalaryLevels.*, 
				SalaryLevels.NameOfSalary, SalaryLevels.Code AS Code_Salary, SalaryLevels.LevelSalary, SalaryLevels.NumberSalary, SalaryLevels.Descriptons,
				Employees.FirstName,  Employees.LastName, Employees.Code AS Code_Employee, Employees.AvatarUrl, Employees.Gender, Employees.Email, Employees.Telephone
FROM            Employees INNER JOIN
				EmployeesSalaryLevels ON Employees.EmpID = EmployeesSalaryLevels.EmpID INNER JOIN
				SalaryLevels ON EmployeesSalaryLevels.SaLevID = SalaryLevels.SaLevID
WHERE			EmployeesSalaryLevels.EmpID = @EmpID
GO



CREATE VIEW View_EmployeesSearch
AS
SELECT						Employees.*, 
							Departments.NameOfDep, 
							Organizations.NameOfOrgan, 
							Communes.NameOfComm, 
							Districts.NameOfDist, 
							Provinces.NameOfProvince, 
							Postions.NameOfPost
FROM						Departments INNER JOIN
							Employees ON Departments.DepID = Employees.DepUsingID INNER JOIN
							Organizations ON Departments.OrgID = Organizations.OrgID INNER JOIN
							Communes ON Organizations.ComID = Communes.ComID INNER JOIN
							Districts ON Communes.DistID = Districts.DistID INNER JOIN
							Provinces ON Districts.ProvID = Provinces.ProvID INNER JOIN
							Postions ON Employees.PosID = Postions.PosID